/**
 * Collaborative Story Development Web Application (CSDApp) Copyright Anton
 * Strack 2014
 *
 * This file is part of Collaborative Story Development Application (CSDApp).
 *
 * CSDApp is free software: you can redistribute it and/or modify it under the
 * terms of the GNU General Public License as published by the Free Software
 * Foundation, either version 3 of the License, or any later version.
 *
 * CSDApp is distributed in the hope that it will be useful, but WITHOUT ANY
 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 * A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License along with
 * CSDApp. If not, see <http://www.gnu.org/licenses/>.
 *
 */
package csdwa;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * This class provides database access to the specific resource model. It
 * provides CRUD database functionality and it stores it's results as a list.
 * Additionally, through it's methods, it provides access to determine the table
 * name, and query markers that are set for the particular DAO and thus allows
 * other code a means of interacting with this DAO to execute more advanced
 * queries.
 *
 * @author Anton Strack
 */
public class AclDao extends DaoSupport {

    private List<Acl> al;

    /**
     * Construct the AclDao
     *
     * @param conn A pre-configured connection to the database.
     */
    public AclDao(Connection conn) {
        super(conn);
        this.al = new ArrayList<Acl>();
        this.restoreQueryMarkers();
    }

    @Override
    public void restoreQueryMarkers() {
        this.setMarker(QueryMarker.CORE_TABLE, "acl");
        this.setMarker(QueryMarker.CORE_COLUMNS, "RoleID, ActionID, ResourceID, IsOwned, IsAllowed");
        this.setMarker(QueryMarker.CORE_CONDITIONS, "RoleID = ? AND ResourceID = ? AND ActionID = ? AND IsOwned = ?");
        this.setMarker(QueryMarker.ORDER_BY_COLUMN, "RoleID");
        this.setMarker(QueryMarker.ORDER_BY_MODE, "DESC");
        this.setMarker(QueryMarker.RELATED_COLUMNS, ", role.name AS RoleName, resourcetype.name AS ResourceName, actiontype.name AS ActionName");
        this.setMarker(QueryMarker.RELATED_CONDITIONS, " AND ResourceID = resourcetype.ID AND ActionID = actiontype.ID AND RoleID = role.ID");
        this.setMarker(QueryMarker.RELATED_TABLES, ", role, resourcetype, actiontype");
        this.setMarker(QueryMarker.LIMIT, "");
        this.setMarker(QueryMarker.INSERT_VALUES, "?, ?, ?, ?, ?");
        this.setMarker(QueryMarker.UPDATE_COLUMNS_VALUES, "IsAllowed = ?");
    }

    public int selectBeans() {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, "1=1");
        try {
            this.ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            result = this.executeSelectQuery(this.ps);
        } catch (SQLException ex) {
            Logger.getLogger(AclDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public int selectBeans(int roleId) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, "RoleID = ?");
        try {
            this.ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, roleId);
            result = this.executeSelectQuery(this.ps);
        } catch (SQLException ex) {
            Logger.getLogger(AclDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public int selectBeans(int roleId, int resourceTypeId) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, "RoleID = ? AND ResourceID = ?");
        try {
            this.ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, roleId);
            this.ps.setInt(2, resourceTypeId);
            result = this.executeSelectQuery(this.ps);
        } catch (SQLException ex) {
            Logger.getLogger(AclDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public int selectBeansByOwnership(int roleId, int ownership) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, "RoleID = ? AND IsOwned = ?");
        try {
            this.ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, roleId);
            this.ps.setInt(2, ownership);
            result = this.executeSelectQuery(this.ps);
        } catch (SQLException ex) {
            Logger.getLogger(AclDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public int selectBeansByOwnership(int roleId, int resourceTypeId, int ownership) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_CONDITIONS, "RoleID = ? AND ResourceID = ? AND IsOwned = ?");
        try {
            this.ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, roleId);
            this.ps.setInt(2, resourceTypeId);
            this.ps.setInt(3, ownership);
            result = this.executeSelectQuery(this.ps);
        } catch (SQLException ex) {
            Logger.getLogger(AclDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public int selectBeansByParentId(int roleId, int parentId) {
        int result = 0;
        this.usingQueryMarkers = this.getCoreSelectUsingQueryMarkers();
        this.setMarker(QueryMarker.CORE_TABLE, "acl, resourcetype");
        this.setMarker(QueryMarker.CORE_CONDITIONS, "RoleID = ? AND resourcetype.ParentID = ? AND resourcetype.ID = ResourceID");
        try {
            this.ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, SELECT_QUERY));
            this.ps.setInt(1, roleId);
            this.ps.setInt(2, parentId);
            result = this.executeSelectQuery(this.ps);
        } catch (SQLException ex) {
            Logger.getLogger(AclDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Get a list of the acls
     *
     * @return a list of access controls
     */
    public List<Acl> getList() {
        return this.al;
    }

    /**
     * Save a bean to the database
     *
     * @param bean the bean to insert. The bean id is not required and won't be
     * used. All other bean values are required.
     * @return the auto-generated id key for the new bean row or 0 on failure.
     */
    public int insertBean(Acl bean) {
        int result = 0;
        ResultSet rs;
        this.usingQueryMarkers = new ArrayList<QueryMarker>();
        this.usingQueryMarkers.add(QueryMarker.CORE_COLUMNS);
        this.usingQueryMarkers.add(QueryMarker.CORE_TABLE);
        this.usingQueryMarkers.add(QueryMarker.INSERT_VALUES);
        /*
         * HSQLDB cannot properly handle insert statements that have table identifiesr in the form of table.columnName so we need to remove the table name for inserts.
         */
        this.queryMarkers.put(QueryMarker.CORE_COLUMNS, this.prepareInsertColumns(this.getMarker(QueryMarker.CORE_COLUMNS)));
        try {
            this.ps = this.getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, INSERT_QUERY));// Acl table doesn't have a single primary key
            this.ps.setInt(1, bean.getRoleId());
            this.ps.setInt(2, bean.getActionId());
            this.ps.setInt(3, bean.getResourceTypeId());
            this.ps.setInt(4, bean.getOwnership());
            this.ps.setBoolean(5, bean.getAccess());
            result = this.ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AclDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Update a bean in the database
     *
     * @param bean the bean to update.
     * @return 1 or 0 indicating if the bean was updated or not.
     */
    public int updateBean(Acl bean) {
        int result = 0;
        this.usingQueryMarkers = new ArrayList<QueryMarker>();
        this.usingQueryMarkers.add(QueryMarker.CORE_TABLE);
        this.usingQueryMarkers.add(QueryMarker.CORE_CONDITIONS);
        this.usingQueryMarkers.add(QueryMarker.UPDATE_COLUMNS_VALUES);
        this.setMarker(QueryMarker.UPDATE_COLUMNS_VALUES, "IsAllowed = ?");
        this.setMarker(QueryMarker.CORE_CONDITIONS, "RoleID = ? AND ResourceID = ? AND ActionID = ? AND IsOwned = ?");

        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, UPDATE_QUERY));
            this.ps.setBoolean(1, bean.getAccess()); // only the access can be updated
            // conditions
            this.ps.setInt(2, bean.getRoleId());
            this.ps.setInt(3, bean.getActionId());
            this.ps.setInt(4, bean.getResourceTypeId());
            this.ps.setInt(5, bean.getOwnership());
            result = this.ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AclDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    /**
     * Deletes the bean from the database and from the dao's beans map.
     *
     * @param Acl the bean to delete
     * @return 1 or 0 indicating if the bean was deleted or not.
     */
    public int deleteBean(Acl bean) {
        int result = 0;
        this.usingQueryMarkers = new ArrayList<QueryMarker>();
        this.usingQueryMarkers.add(QueryMarker.CORE_CONDITIONS);
        this.usingQueryMarkers.add(QueryMarker.CORE_TABLE);
        this.setMarker(QueryMarker.CORE_CONDITIONS, "RoleID = ? AND ResourceID = ? AND ActionID = ? AND IsOwned = ?");
        try {
            ps = getConn().prepareStatement(this.buildQueryString(usingQueryMarkers, DELETE_QUERY));
            this.ps.setInt(1, bean.getRoleId());
            this.ps.setInt(2, bean.getActionId());
            this.ps.setInt(3, bean.getResourceTypeId());
            this.ps.setInt(4, bean.getOwnership());
            result = this.ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(AclDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    @Override
    protected int executeSelectQuery(PreparedStatement ps) {
        int result = 0;
        ResultSet rs;
        try {
            rs = ps.executeQuery();
            while (rs.next()) {
                Acl acl = new Acl();
                acl.setAccess(rs.getBoolean("IsAllowed"));
                acl.setActionId(rs.getInt("ActionID"));
                acl.setOwnership(rs.getInt("IsOwned"));
                acl.setResourceTypeId(rs.getInt("ResourceID"));
                acl.setRoleId(rs.getInt("RoleID"));
                if (this.USE_RELATED_MARKERS) {
                    acl.setIdNames("ActionName", rs.getString("ActionName"));
                    acl.setIdNames("RoleName", rs.getString("RoleName"));
                    acl.setIdNames("ResourceName", rs.getString("ResourceName"));
                }
                this.al.add(acl);
                result++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(AclDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
}
